package com.ding.dao.Impl;

import com.ding.dao.BaseDao;
import com.ding.dao.StudentDao;
import com.ding.entity.Student;
import com.ding.util.Page;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl extends BaseDao implements StudentDao {
    @Override
    public int count(String stuName, Integer classId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT COUNT(*) count");
        sb.append(" FROM student s,class c ");
        sb.append(" WHERE");
        sb.append(" s.classId = c.id ");
        List<Object> parms = new ArrayList<>();
        if (stuName != null && !"".equals(stuName)) {
            sb.append(" AND s.userName ");
            sb.append(" LIKE CONCAT('%',?,'%')");
            parms.add(stuName);
        }
        if (classId != null && classId != -1) {
            sb.append(" AND c.id  = ?");
            parms.add(classId);
        }
        ResultSet resultSet = executeQuery(sb.toString(), parms.toArray());
        try {
            while (resultSet.next()) {
                int count = resultSet.getInt("count");
                return count;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closedAll(resultSet);
        }

        return 0;
    }

    @Override
    public List<Student> selectList(Page page, String stuName, Integer classId) {

        List<Student> studentList = new ArrayList<>();

        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT s.*,c.className, ");
        sb.append(" FLOOR(DATEDIFF(NOW(),s.birthday)/365 ) age");
        sb.append(" FROM student s, class c ");
        sb.append(" WHERE");
        sb.append(" s.classId = c.id ");
        List<Object> parms = new ArrayList<>();
        if (stuName != null && !"".equals(stuName)) {
            sb.append(" AND s.userName ");
            sb.append(" LIKE CONCAT('%',?,'%')");
            parms.add(stuName);
        }
        if (classId != null && classId != -1) {
            sb.append(" AND c.id  = ?");
            parms.add(classId);
        }
        sb.append(" LIMIT ?,?");
        parms.add((page.getPageNum() - 1)*page.getCount());
        parms.add(page.getCount());


        ResultSet resultSet = executeQuery(sb.toString(), parms.toArray());
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    Student student = new Student();
                    student.setId(resultSet.getInt("id"));
                    student.setUserName(resultSet.getString("userName"));
                    student.setImgPath(resultSet.getString("imgPath"));
                    student.setClassId(resultSet.getInt("classId"));
                    student.setClassName(resultSet.getString("className"));
                    student.setSex(resultSet.getString("sex"));
                    student.setPhone(resultSet.getString("phone"));
                    student.setAge(resultSet.getInt("age"));
                    student.setBirthday(resultSet.getString("birthday"));
                    student.setAddress(resultSet.getString("address"));
                    student.setState(resultSet.getString("state"));
                    studentList.add(student);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closedAll(resultSet);
            }
        }

        return studentList;
    }

    @Override
    public Student stuDetail(int id) {
        String sql = "select * from student s where s.id = ?";
        ResultSet resultSet = executeQuery(sql, id);
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    Student student = new Student();
                    student.setId(resultSet.getInt("id"));
                    student.setUserName(resultSet.getString("userName"));
                    student.setImgPath(resultSet.getString("imgPath"));
                    student.setClassId(resultSet.getInt("classId"));
                    student.setSex(resultSet.getString("sex"));
                    student.setPhone(resultSet.getString("phone"));
                    student.setBirthday(resultSet.getString("birthday"));
                    student.setAddress(resultSet.getString("address"));
                    student.setState(resultSet.getString("state"));
                    return student;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closedAll(resultSet);
            }
        }

        return null;
    }

    @Override
    public int addStu(Student student) {
        StringBuilder sb = new StringBuilder();
        sb.append(" INSERT into student (");
        sb.append(" student.userName,student.sex,student.phone,");
        sb.append(" student.imgPath,student.classId,student.birthday,student.address)");
        sb.append(" VALUES(?,?,?,?,?,?,?)");

        return executeUpdate(sb.toString(),
                student.getUserName(), student.getSex(), student.getPhone(),
                student.getImgPath(), student.getClassId(), student.getBirthday(), student.getAddress());
    }

    @Override
    public int updateStu(Student student) {
        StringBuilder sb = new StringBuilder();

        sb.append(" UPDATE student SET");
        sb.append(" userName = ?,imgPath = ?, classId = ?,");
        sb.append(" sex=?,phone=?,birthday=?,address=?");
        sb.append(" WHERE id = ?");
        return executeUpdate(sb.toString(), student.getUserName(),
                student.getImgPath(), student.getClassId(),
                student.getSex(), student.getPhone(), student.getBirthday(),
                student.getAddress(), student.getId());

    }

    @Override
    public int delStu(Integer id) {
        String sql = "DELETE FROM student WHERE id = ?";
        return executeUpdate(sql,id);
    }
}
